import random
import time

from PyQt5.QtCore import pyqtSignal, QObject

from Manage.db_connector import DBUtils
import mysql.connector

class CardManager(QObject):
    # 定义银行卡变更信号
    cardChanged = pyqtSignal()

    def __init__(self,parent=None):
        super().__init__(parent)
        self.db = DBUtils()
        self.db.connect()

    def __del__(self):
        self.db.disconnect()

    def add_card(self, card_id, cur_id, saving_id, customer_id,
                 open_money=1.0, balance=1.0, password='888888', is_report_loss='否'):
        """
        添加银行卡 - 增强版，支持选择客户ID并检查客户存在
        参数:
            card_id: 卡号(17位)
            cur_id: 货币类型
            saving_id: 存款类型ID
            customer_id: 客户ID（可以是None，表示需要选择客户）
            open_money: 开户金额
            balance: 余额
            password: 密码(6位)
            is_report_loss: 是否挂失
        """
        # === 新增功能：支持客户ID选择 ===
        if customer_id is None:
            # 获取所有客户
            customer_query = "SELECT customerID, name FROM userinfo"
            customers = self.db.execute_query(customer_query)

            # 如果没有客户，显示错误
            if not customers:
                print("没有可用的客户信息")
                return False

            # 创建客户选择对话框（在控制台界面）
            print("\n请选择客户:")
            for idx, customer in enumerate(customers, start=1):
                print(f"{idx}. {customer.get('name', '')} (ID: {customer.get('customerID', '')})")

            # 获取用户选择
            selected_idx = input("请输入选择的客户编号: ")

            # 验证输入
            try:
                selected_idx = int(selected_idx) - 1
                if 0 <= selected_idx < len(customers):
                    customer_id = customers[selected_idx].get('customerID')
                    print(f"选择的客户ID: {customer_id}")
                else:
                    print("无效的客户编号")
                    return False
            except ValueError:
                print("输入无效，请输入数字")
                return False

            # 如果仍然没有有效的客户ID
            if not customer_id:
                print("无法确定客户ID")
                return False

        # === 新增功能：检查客户是否存在 ===
        # 查询客户信息
        customer_query = "SELECT * FROM userinfo WHERE customerID = %s"
        customer_result = self.db.execute_query(customer_query, (customer_id,))

        # 检查客户是否存在
        if not customer_result:
            print(f"客户ID {customer_id} 不存在，无法添加银行卡")
            return False

        # 原有的添加逻辑保持不变
        query = """
        INSERT INTO cardInfo 
        (cardID, curID, savingID, customerID, openMoney, balance, pass, IsReportLoss)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
        """
        params = (card_id, cur_id, saving_id, customer_id,
                  open_money, balance, password, is_report_loss)

        try:
            result = self.db.execute_query(query, params, fetch=False)
            if result:
                print(f"成功添加银行卡: {card_id}，所属客户ID: {customer_id}")
                return True
            return False
        except mysql.connector.Error as e:
            # 特定处理主键冲突错误
            if e.errno == mysql.connector.errorcode.ER_DUP_ENTRY:
                if 'uk_user_customerID' in str(e):
                    print(f"错误: 客户ID {customer_id} 已经存在相同类型的银行卡")
                else:
                    print(f"银行卡 {card_id} 已存在")
            else:
                print(f"添加银行卡失败: {str(e)}")
            return False

    def delete_card(self, card_id):
        """
        删除银行卡
        检查是否有未完成的交易记录
        """
        # 检查是否有未完成交易
        check_query = """
        SELECT COUNT(*) as count FROM tradeInfo 
        WHERE cardID = %s AND tradeType IN ('支出','收入')
        """
        check_result = self.db.execute_query(check_query, (card_id,))

        if check_result and check_result[0]['count'] > 0:
            print("该卡有未完成的交易记录，不能删除")
            return False

        query = "DELETE FROM cardInfo WHERE cardID = %s"
        try:
            result = self.db.execute_query(query, (card_id,), fetch=False)
            if result:
                print(f"成功删除银行卡 {card_id}")
                return True
            print("未找到该银行卡")
            return False
        except Exception as e:
            print(f"删除银行卡失败: {e}")
            return False

    def get_card_by_id(self, card_id):
        """根据卡号查询银行卡信息"""
        query = "SELECT * FROM cardInfo WHERE cardID = %s"
        result = self.db.execute_query(query, (card_id,))
        return result[0] if result else None

    def get_customer_cards(self, customer_id):
        """查询客户的所有银行卡"""
        query = """
        SELECT c.*, d.savingName 
        FROM cardInfo c
        JOIN deposit d ON c.savingID = d.savingID
        WHERE c.customerID = %s
        """
        return self.db.execute_query(query, (customer_id,))

    def list_all_cards(self):
        """获取所有银行卡信息"""
        query = """
        SELECT c.*, d.savingName, u.customerName
        FROM cardInfo c
        LEFT JOIN deposit d ON c.savingID = d.savingID
        LEFT JOIN userInfo u ON c.customerID = u.customerID
        ORDER BY c.cardID
        """
        return self.db.execute_query(query)

    def update_card(self, card_id, **kwargs):
        """
        更新银行卡信息 - 健壮实现
        :param card_id: 要更新的卡号
        :param kwargs: 更新参数
        """
        # 参数验证
        if not card_id:
            print("错误: 卡号不能为空")
            return False

        # 有效字段映射
        valid_fields = {
            'cur_id': 'curID',
            'saving_id': 'savingID',
            'balance': 'balance',
            'password': 'pass',
            'is_report_loss': 'IsReportLoss'
        }

        # 过滤无效字段
        updates = []
        params = []
        for key, value in kwargs.items():
            if key in valid_fields:
                # 类型转换和处理
                if key == 'balance':
                    try:
                        value = float(value)
                    except (ValueError, TypeError):
                        print(f"无效的余额值: {value}")
                        continue
                elif key == 'saving_id':
                    try:
                        value = int(value)  # 确保saving_id是整数
                    except (ValueError, TypeError):
                        print(f"无效的存款类型ID: {value}")
                        continue

                updates.append(f"{valid_fields[key]} = %s")
                params.append(value)

        # 检查是否有有效更新
        if not updates:
            print("错误: 没有有效更新字段")
            return False

        # 构建查询
        query = f"UPDATE cardInfo SET {', '.join(updates)} WHERE cardID = %s"
        params.append(card_id)

        # 打印调试信息
        print(f"执行更新查询: {query}")
        print(f"参数: {params}")

        try:
            # 执行更新
            result = self.db.execute_query(query, params, fetch=False)
            if result:
                print(f"成功更新银行卡 {card_id}")
                self.cardChanged.emit()
                return True
            else:
                print("更新失败 - 可能是卡号不存在或数据未变更")
                return False
        except Exception as e:
            print(f"更新银行卡时发生异常: {str(e)}")
            return False

    def _generate_trade_id(self):
        """生成唯一交易流水号"""
        return f"TR{int(time.time() * 1000)}{random.randint(100, 999)}"

    def get_current_cards(self, search_keyword=None, advanced_params=None):
        """
        获取当前银行卡数据（带搜索条件）
        :param search_keyword: 基本搜索关键字
        :param advanced_params: 高级搜索参数
        """
        try:
            if advanced_params:
                return self.advanced_search(advanced_params)
            elif search_keyword:
                return self.basic_search(search_keyword)
            else:
                return self.list_all_cards()
        except Exception as e:
            print(f"获取银行卡数据失败: {str(e)}")
            return []

    def basic_search(self, keyword):
        """基本搜索"""
        query = """
        SELECT c.cardID, c.curID, d.savingName, c.customerID, 
               c.openMoney, c.balance, c.IsReportLoss
        FROM cardInfo c
        JOIN deposit d ON c.savingID = d.savingID
        WHERE c.cardID LIKE %s
           OR c.curID LIKE %s
           OR d.savingName LIKE %s
           OR c.customerID LIKE %s
           OR c.openMoney LIKE %s
           OR c.balance LIKE %s
           OR c.IsReportLoss LIKE %s
        ORDER BY c.cardID
        """
        params = tuple([f"%{keyword}%"] * 7)
        return self.db.execute_query(query, params)

    def advanced_search(self, params):
        """高级搜索"""
        query = """
        SELECT c.cardID, c.curID, d.savingName, c.customerID, 
               c.openMoney, c.balance, c.IsReportLoss
        FROM cardInfo c
        JOIN deposit d ON c.savingID = d.savingID
        WHERE 1=1
        """
        query_params = []

        # 添加条件
        if params.get('card_id'):
            query += " AND c.cardID LIKE %s"
            query_params.append(f"%{params['card_id']}%")

        if params.get('cur_type'):
            query += " AND c.curID LIKE %s"
            query_params.append(f"%{params['cur_type']}%")

        if params.get('saving_type'):
            query += " AND d.savingName LIKE %s"
            query_params.append(f"%{params['saving_type']}%")

        if params.get('customer_id'):
            query += " AND c.customerID LIKE %s"
            query_params.append(f"%{params['customer_id']}%")

        if params.get('min_open_money') is not None:
            query += " AND c.openMoney >= %s"
            query_params.append(float(params['min_open_money']))

        if params.get('max_open_money') is not None:
            query += " AND c.openMoney <= %s"
            query_params.append(float(params['max_open_money']))

        if params.get('min_balance') is not None:
            query += " AND c.balance >= %s"
            query_params.append(float(params['min_balance']))

        if params.get('max_balance') is not None:
            query += " AND c.balance <= %s"
            query_params.append(float(params['max_balance']))

        if params.get('is_report_loss'):
            query += " AND c.IsReportLoss = %s"
            query_params.append(params['is_report_loss'])

        query += " ORDER BY c.cardID"

        return self.db.execute_query(query, query_params)

    def list_all_cards(self):
        """获取所有银行卡"""
        query = """
        SELECT c.cardID, c.curID, d.savingName, c.customerID, 
               c.openMoney, c.balance, c.IsReportLoss
        FROM cardInfo c
        JOIN deposit d ON c.savingID = d.savingID
        ORDER BY c.cardID
        """
        return self.db.execute_query(query)